CREATE OR REPLACE PACKAGE  "CONTENT" AS

-- stored procedures
PROCEDURE InsertContent
(
    inFileName IN SDR_CONTENT.FILE_NAME%TYPE,
    inData IN SDR_CONTENT.DATA%TYPE,
    inContentTypeCode IN SDR_CONTENT_TYPE.CODE%TYPE,
    outId OUT SDR_CONTENT.ID%TYPE
);

PROCEDURE GetContentById
(
    inContentId IN SDR_CONTENT.ID%TYPE,
    outCursor OUT SYS_REFCURSOR
);

PROCEDURE SetContentStatus
(
    inContentId IN SDR_CONTENT.ID%TYPE,
    inContentStatusCode IN SDR_CONTENT_STATUS.CODE%TYPE,
    outResult OUT INT
);

END CONTENT;
/
CREATE OR REPLACE PACKAGE BODY  "CONTENT" AS

/*
 * Description : Inserts a new content into the database
 * Author : Lukasz Laszko
 * Params : inFileName - file name
 *          inData  - dcontent binary data
 *          inContentTypeCode - content type code
 *          outId - id of newly created content or error code
 * Return values: outId  - >0  -  all ok, content id
 *                         -1  -  invalid content type code
 * History : 2008-05-11 - Lukasz Laszko, stored procedure created
 */
PROCEDURE InsertContent
(
    inFileName IN SDR_CONTENT.FILE_NAME%TYPE,
    inData IN SDR_CONTENT.DATA%TYPE,
    inContentTypeCode IN SDR_CONTENT_TYPE.CODE%TYPE,
    outId OUT SDR_CONTENT.ID%TYPE
)
AS
    contentTypeCodeExists INT;
    contentTypeId SDR_CONTENT_TYPE.ID%TYPE;
BEGIN
    -- check if content type code is valid
    SELECT COUNT(*) INTO contentTypeCodeExists
    FROM SDR_CONTENT_TYPE
    WHERE (CODE = inContentTypeCode) AND (ROWNUM <= 1);
    IF (contentTypeCodeExists = 0) THEN
        outId := -1;
        RETURN;
    END IF;
    
    -- get contentTypeId
    SELECT ID INTO contentTypeId
    FROM SDR_CONTENT_TYPE
    WHERE (CODE = inContentTypeCode) AND (ROWNUM <= 1);

    -- insert content
    INSERT INTO SDR_CONTENT(FILE_NAME, DATA, CONTENT_TYPE_ID)
                     VALUES(inFileName, inData, contentTypeId)
    RETURNING ID INTO outId;                     
    
END InsertContent;

/*
 * Description : Gets content by its unique id
 * Author : Lukasz Laszko
 * Params : inContentId - content unique id
 *          outCursor - reference cursor to the result set
 * History : 2008-05-12 - Lukasz Laszko, stored procedure created
 */
PROCEDURE GetContentById
(
    inContentId IN SDR_CONTENT.ID%TYPE,
    outCursor OUT SYS_REFCURSOR
)
AS
BEGIN
    
    OPEN outCursor FOR 
      SELECT  SDR_CONTENT.ID,
              SDR_CONTENT.FILE_NAME,
              SDR_CONTENT.DATA,
              SDR_CONTENT_TYPE.CODE AS TYPE_CODE,
              (SELECT CODE
               FROM SDR_CONTENT_STATUS
               WHERE ID = (SELECT CONTENT_STATUS_ID
                           FROM SDR_CONTENT_STATUS_HISTORY
                           WHERE ID = (SELECT MAX(ID)
                                       FROM SDR_CONTENT_STATUS_HISTORY
                                       WHERE CONTENT_ID = SDR_CONTENT.ID))) AS STATUS_CODE,
             (SELECT ADATE
              FROM SDR_CONTENT_STATUS_HISTORY
              WHERE ID = (SELECT MAX(ID)
                          FROM SDR_CONTENT_STATUS_HISTORY
                          WHERE CONTENT_ID = SDR_CONTENT.ID)) AS STATUS_DATE
      FROM SDR_CONTENT INNER JOIN SDR_CONTENT_TYPE ON SDR_CONTENT.CONTENT_TYPE_ID = SDR_CONTENT_TYPE.ID
      WHERE SDR_CONTENT.ID = inContentId;
    
END GetContentById;

/*
 * Description : Sets content status
 * Author : Lukasz Laszko
 * Params : inContentStatusCode - content statuscode
 *          outResult - error code
 * Returns values :  0  - all ok
 *                  -1  - invalid content id
 *                  -2  - invalid content status code
 * History : 2008-05-15 - Lukasz Laszko, stored procedure created
 */
PROCEDURE SetContentStatus
(
    inContentId IN SDR_CONTENT.ID%TYPE,
    inContentStatusCode IN SDR_CONTENT_STATUS.CODE%TYPE,
    outResult OUT INT
)
AS
    contentExists INT;
    contentStatusCodeExists INT;
    contentStatusId SDR_CONTENT_STATUS.ID%TYPE;
BEGIN
    -- check if content exists
    SELECT COUNT(*) INTO contentExists
    FROM SDR_CONTENT
    WHERE ID = inContentId;
    IF (contentExists = 0) THEN
        outResult := -1;
        RETURN;
    END IF;
    
    -- check if content status code exists
    SELECT COUNT(*) INTO contentStatusCodeExists
    FROM SDR_CONTENT_STATUS
    WHERE (CODE = inContentStatusCode) AND (ROWNUM <= 1);
    IF (contentStatusCodeExists = 0) THEN
        outResult := -2;
        RETURN;
    END IF;
    
    -- get content status id
    SELECT ID INTO contentStatusId 
    FROM SDR_CONTENT_STATUS
    WHERE (CODE = inContentStatusCode) AND (ROWNUM <= 1);
    
    -- insert status
    INSERT INTO SDR_CONTENT_STATUS_HISTORY (CONTENT_ID, CONTENT_STATUS_ID, ADATE)
                                    VALUES (inContentId, contentStatusId, CURRENT_TIMESTAMP);
    
    outResult := 0;
END SetContentStatus;

END CONTENT;
/
 CREATE OR REPLACE PACKAGE  "DOCUMENT" AS

PROCEDURE InsertDocument
(
    inTitle IN SDR_DOCUMENT.TITLE%TYPE,
    inDescription IN SDR_DOCUMENT.DESCRIPTION%TYPE,
    inContentId IN SDR_DOCUMENT.CONTENT_ID%TYPE,
    inReference IN SDR_DOCUMENT.REFERENCE%TYPE,
    inDocumentTypeCode IN SDR_DOCUMENT_TYPE.CODE%TYPE,    
    inLanguageCode IN SDR_LANGUAGE.CODE%TYPE,
    inOriginalId IN SDR_DOCUMENT.ORIGINAL_ID%TYPE,
    outId OUT SDR_DOCUMENT.ID%TYPE
);

PROCEDURE GetDocumentById
(
    inDocumentId IN SDR_DOCUMENT.ID%TYPE,
    outCursor OUT SYS_REFCURSOR
);

PROCEDURE SetDocumentStatus
(
    inDocumentId IN SDR_DOCUMENT.ID%TYPE,
    inDocumentStatusCode IN SDR_DOCUMENT_STATUS.CODE%TYPE,
    inPersonId IN SDR_PERSON.ID%TYPE,
    outResult OUT INT
);

PROCEDURE GetLanguageCodes
(
    outCursor OUT SYS_REFCURSOR
);

PROCEDURE GetDocumentsByStatus
(
    inDocumentStatusCode IN SDR_DOCUMENT_STATUS.CODE%TYPE,
    inDocumentStatusAuthorId IN SDR_PERSON.ID%TYPE,
    outResult OUT INT,
    outCursor OUT SYS_REFCURSOR
);

PROCEDURE GetReplacedDocuments
(
    inDocumentId IN SDR_DOCUMENT.ID%TYPE,
    outResult OUT INT,
    outCursor OUT SYS_REFCURSOR
);

END DOCUMENT;
/
CREATE OR REPLACE PACKAGE BODY  "DOCUMENT" AS

/*
 * Description : Inserts a new document into the database
 * Author : Lukasz Laszko
 * Params : inTitle - document title
 *          inDescription - textual description of the document
 *          inContentId - id of document binary content
 *          inReference - reference to external storage system
 *          inDocumentTypeCode  - document type code
 *          inLanguageCode  - document language code
 *          inOriginalId  - id of the document replaced by this document
 *          outId - id of newly created document or error code
 * Return values: id  - >0  - all ok, document id
 *                      -1  - invalid document type code
 *                      -2  - invalid language code
 *                      -3  - invalid inOriginalId, document doesn not exist 
 * History : 2008-05-11 - Lukasz Laszko, stored procedure created
 */
PROCEDURE InsertDocument
(
    inTitle IN SDR_DOCUMENT.TITLE%TYPE,
    inDescription IN SDR_DOCUMENT.DESCRIPTION%TYPE,
    inContentId IN SDR_DOCUMENT.CONTENT_ID%TYPE,
    inReference IN SDR_DOCUMENT.REFERENCE%TYPE,
    inDocumentTypeCode IN SDR_DOCUMENT_TYPE.CODE%TYPE,    
    inLanguageCode IN SDR_LANGUAGE.CODE%TYPE,
    inOriginalId IN SDR_DOCUMENT.ORIGINAL_ID%TYPE,
    outId OUT SDR_DOCUMENT.ID%TYPE
) 
AS
    documentTypeCodeExists INT;
    documentTypeId SDR_DOCUMENT_TYPE.ID%TYPE;
    languageCodeExists INT;
    languageId SDR_LANGUAGE.ID%TYPE;
    originalExists INT;
BEGIN
    --  check if document type code is valid
    SELECT COUNT(*) INTO documentTypeCodeExists
    FROM SDR_DOCUMENT_TYPE
    WHERE (CODE = inDocumentTypeCode) AND (ROWNUM <= 1);
    IF (documentTypeCodeExists = 0) THEN
        outId := -1;
        RETURN;
    END IF;
    
    -- get documentTypeId
    SELECT ID INTO documentTypeId
    FROM SDR_DOCUMENT_TYPE
    WHERE (CODE = inDocumentTypeCode) AND (ROWNUM <= 1);
    
    -- check if language exists
    SELECT COUNT(*) INTO languageCodeExists
    FROM SDR_LANGUAGE
    WHERE (CODE = inLanguageCode) AND (ROWNUM <= 1);
    IF (languageCodeExists = 0) THEN
        outId := -2;
        RETURN;
    END IF;
    
    -- get language id
    SELECT ID INTO languageId
    FROM SDR_LANGUAGE
    WHERE (CODE = inLanguageCode) AND (ROWNUM <= 1);
    
    -- chck if document exists if original document defined
    IF (inOriginalId IS NOT NULL) THEN
        SELECT COUNT(*) INTO originalExists    
        FROM SDR_DOCUMENT
        WHERE (ID = inOriginalId);
        IF (originalExists = 0) THEN
            outId := -3;
            RETURN;
        END IF;
    END IF;
    
    -- insert document
    INSERT INTO SDR_DOCUMENT(TITLE, 
                             DESCRIPTION,
                             CONTENT_ID,
                             REFERENCE,
                             DOCUMENT_TYPE_ID,
                             LANGUAGE_ID,
                             ORIGINAL_ID)
                      VALUES(inTitle,
                             inDescription,
                             inContentId,
                             inReference,
                             documentTypeId,
                             languageId,
                             inOriginalId)
    RETURNING ID INTO outId;
END InsertDocument;

/*
 * Description : Gets document by its id
 * Author : Lukasz Laszko
 * Params : inDocumentId  - document unique id 
 *          outCursor - reference cursor to output set
 * History : 2008-05-11 - Lukasz Laszko, stored procedure created
 */
PROCEDURE GetDocumentById
(
    inDocumentId IN SDR_DOCUMENT.ID%TYPE,
    outCursor OUT SYS_REFCURSOR
)
AS
BEGIN
    
    OPEN outCursor FOR
      SELECT SDR_DOCUMENT.ID,
             SDR_DOCUMENT.TITLE,
             SDR_DOCUMENT.DESCRIPTION,
             SDR_DOCUMENT.CONTENT_ID,
             SDR_DOCUMENT.REFERENCE,
             SDR_DOCUMENT_TYPE.CODE AS TYPE_CODE,
             SDR_LANGUAGE.CODE AS LANGUAGE_CODE,
             SDR_DOCUMENT.ORIGINAL_ID,
             (SELECT CODE
              FROM SDR_DOCUMENT_STATUS
              WHERE ID = (SELECT DOCUMENT_STATUS_ID
                          FROM SDR_DOCUMENT_STATUS_HISTORY
                          WHERE ID = (SELECT MAX(ID)
                                      FROM SDR_DOCUMENT_STATUS_HISTORY
                                      WHERE DOCUMENT_ID = SDR_DOCUMENT.ID))) AS STATUS_CODE,
             (SELECT ADATE
              FROM SDR_DOCUMENT_STATUS_HISTORY
              WHERE ID = (SELECT MAX(ID)
                          FROM SDR_DOCUMENT_STATUS_HISTORY
                          WHERE DOCUMENT_ID = SDR_DOCUMENT.ID)) AS STATUS_DATE,
             (SELECT LOGIN
              FROM SDR_PERSON
              WHERE ID = (SELECT PERSON_ID
                          FROM SDR_DOCUMENT_STATUS_HISTORY
                          WHERE ID = (SELECT MAX(ID)
                                      FROM SDR_DOCUMENT_STATUS_HISTORY
                                      WHERE DOCUMENT_ID = SDR_DOCUMENT.ID))) AS STATUS_AUTHOR_LOGIN
      FROM SDR_DOCUMENT INNER JOIN SDR_DOCUMENT_TYPE ON SDR_DOCUMENT.DOCUMENT_TYPE_ID = SDR_DOCUMENT_TYPE.ID
                        INNER JOIN SDR_LANGUAGE ON SDR_DOCUMENT.LANGUAGE_ID = SDR_LANGUAGE.ID
      WHERE SDR_DOCUMENT.ID = inDocumentId;                     
          
    
END GetDocumentById;

/*
 * Description : Sets the document status
 * Author : Lukasz Laszko
 * Params : inDocumentId  - document unique id
 *          inDocumentStatusCode - document status code
 *          outResult - result code
 * Return values: outResult  - 0  - all ok
 *                             -1 - invalid document id
 *                             -2 - invalid document status code
 *                             -3 - person doesnot exist
 * History : 2008-05-11 - Lukasz Laszko, stored procedure created
 */
PROCEDURE SetDocumentStatus
(
    inDocumentId IN SDR_DOCUMENT.ID%TYPE,
    inDocumentStatusCode IN SDR_DOCUMENT_STATUS.CODE%TYPE,
    inPersonId IN SDR_PERSON.ID%TYPE,
    outResult OUT INT
)
AS
    documentExists INT;
    documentStatusCodeExists INT;
    documentStatusId SDR_DOCUMENT_STATUS.ID%TYPE;
    personExists INT;
BEGIN
    -- check if document exists
    SELECT COUNT(*) INTO documentExists
    FROM SDR_DOCUMENT
    WHERE (ID = inDocumentId);
    IF (documentExists = 0) THEN
        outResult := -1;
        RETURN;
    END IF;
    
    -- check if document status code exists
    SELECT COUNT(*) INTO documentStatusCodeExists
    FROM SDR_DOCUMENT_STATUS
    WHERE (CODE = inDocumentStatusCode) AND (ROWNUM <= 1);
    IF (documentStatusCodeExists = 0) THEN
        outResult := -2;
        RETURN;
    END IF;
    
    -- get documentStatusId
    SELECT ID INTO documentStatusId
    FROM SDR_DOCUMENT_STATUS
    WHERE (CODE = inDocumentStatusCode) AND (ROWNUM <= 1);
    
    -- check if person exists
    SELECT COUNT(*) INTO personExists
    FROM SDR_PERSON
    WHERE (ID = inPersonId);
    IF (personExists = 0) THEN
        outResult := -3;
        RETURN;
    END IF;
    
    -- insert new status
    INSERT INTO SDR_DOCUMENT_STATUS_HISTORY(DOCUMENT_ID, DOCUMENT_STATUS_ID, PERSON_ID, ADATE)
                                     VALUES(inDocumentId, documentStatusId, inpersonId, CURRENT_TIMESTAMP);
    
    outResult := 0;
END SetDocumentStatus;

/*
 * Description : Gets list of langage codes stored in the database
 * Author : Lukasz Laszko
 * Params : outCursor - ref cursor to result set
 * History : 2008-05-11 - Lukasz Laszko, stored procedure created
 */
PROCEDURE GetLanguageCodes
(
    outCursor OUT SYS_REFCURSOR
)
AS
BEGIN
    OPEN outCursor FOR
      SELECT CODE
      FROM SDR_LANGUAGE;
END GetLanguageCodes;

/*
 * Description : Gets documents by active status and status author
 * Author : Lukasz Laszko
 * Params : inDocumentStatusCode  - document status code
 *          inDocumentStatusAuthorId - document status author id
 *          outResult - result code
 *          outCursor - reference cursor to result set
 * Return values: outResult  - 0  - all ok
 *                             -1 - invalid document id
 *                             -2 - invalid document status code
 *                             -3 - person doesnot exist
 * History : 2008-05-11 - Lukasz Laszko, stored procedure created
 */
PROCEDURE GetDocumentsByStatus
(
    inDocumentStatusCode IN SDR_DOCUMENT_STATUS.CODE%TYPE,
    inDocumentStatusAuthorId IN SDR_PERSON.ID%TYPE,
    outResult OUT INT,
    outCursor OUT SYS_REFCURSOR
)
AS
    documentStatusCodeExists INT;
    documentStatusId SDR_DOCUMENT_STATUS.ID%TYPE;
    personExists INT;    
BEGIN
    -- check if status code exists
    SELECT COUNT(*) INTO documentStatusCodeExists
    FROM SDR_DOCUMENT_STATUS
    WHERE (CODE = inDocumentStatusCode) AND (ROWNUM <= 1);
    IF (documentStatusId = 0) THEN
        outResult := -1;
        RETURN;
    END IF;
    
    -- get document status id
    SELECT ID INTO documentStatusId
    FROM SDR_DOCUMENT_STATUS
    WHERE (CODE = inDocumentStatusCode) AND (ROWNUM <= 1);
    
    -- check if status author exists
    SELECT COUNT(*) INTO personExists
    FROM SDR_PERSON
    WHERE (ID = inDocumentStatusAuthorId);
    IF (personExists = 0) THEN
        outResult := -2;
        RETURN;
    END IF;
    
    -- select documents
    OPEN outCursor FOR 
      SELECT SDR_DOCUMENT.ID,
             SDR_DOCUMENT.TITLE,
             SDR_DOCUMENT.DESCRIPTION,
             SDR_DOCUMENT.CONTENT_ID,
             SDR_DOCUMENT.REFERENCE,
             SDR_DOCUMENT_TYPE.CODE AS TYPE_CODE,
             SDR_LANGUAGE.CODE AS LANGUAGE_CODE,
             SDR_DOCUMENT.ORIGINAL_ID,
             (SELECT CODE
              FROM SDR_DOCUMENT_STATUS
              WHERE ID = (SELECT DOCUMENT_STATUS_ID
                          FROM SDR_DOCUMENT_STATUS_HISTORY
                          WHERE ID = (SELECT MAX(ID)
                                      FROM SDR_DOCUMENT_STATUS_HISTORY
                                      WHERE DOCUMENT_ID = SDR_DOCUMENT.ID))) AS STATUS_CODE,
             (SELECT ADATE
              FROM SDR_DOCUMENT_STATUS_HISTORY
              WHERE ID = (SELECT MAX(ID)
                          FROM SDR_DOCUMENT_STATUS_HISTORY
                          WHERE DOCUMENT_ID = SDR_DOCUMENT.ID)) AS STATUS_DATE,
             (SELECT LOGIN
              FROM SDR_PERSON
              WHERE ID = (SELECT PERSON_ID
                          FROM SDR_DOCUMENT_STATUS_HISTORY
                          WHERE ID = (SELECT MAX(ID)
                                      FROM SDR_DOCUMENT_STATUS_HISTORY
                                      WHERE DOCUMENT_ID = SDR_DOCUMENT.ID))) AS STATUS_AUTHOR_LOGIN
      FROM SDR_DOCUMENT INNER JOIN SDR_DOCUMENT_TYPE ON SDR_DOCUMENT.DOCUMENT_TYPE_ID = SDR_DOCUMENT_TYPE.ID
                        INNER JOIN SDR_LANGUAGE ON SDR_DOCUMENT.LANGUAGE_ID = SDR_LANGUAGE.ID
                        INNER JOIN (SELECT DOCUMENT_ID, 
                                           DOCUMENT_STATUS_ID, 
                                           PERSON_ID
                                    FROM SDR_DOCUMENT_STATUS_HISTORY
                                    WHERE (DOCUMENT_ID = (SELECT DOCUMENT_ID
                                                          FROM SDR_DOCUMENT_STATUS_HISTORY
                                                          WHERE (DOCUMENT_STATUS_ID = documentStatusId) AND 
                                                                (PERSON_ID = inDocumentStatusAuthorId))) AND
                                          (ROWNUM <= 1)                                                                
                                    ORDER BY ID DESC) VALID_DOCUMENTS ON SDR_DOCUMENT.ID = VALID_DOCUMENTS.DOCUMENT_ID                             
    WHERE (VALID_DOCUMENTS.DOCUMENT_STATUS_ID = documentStatusId) AND 
          (VALID_DOCUMENTS.PERSON_ID = inDocumentStatusAuthorId);
          
    outResult := 0;
END GetDocumentsByStatus;

/*
 * Description : Gets list of documents replaced by the document
 * Author : Lukasz Laszko
 * Params : inDocumentId  - document id
 *          outResult - result code
 *          outCursor - reference cursor to result set
 * Return values: outResult  - 0  - all ok
 *                             -1 - invalid document id
 * History : 2008-05-11 - Lukasz Laszko, stored procedure created
 */
PROCEDURE GetReplacedDocuments
(
    inDocumentId IN SDR_DOCUMENT.ID%TYPE,
    outResult OUT INT,
    outCursor OUT SYS_REFCURSOR
)
AS
    documentExists INT;
BEGIN
    -- check if document exists
    SELECT COUNT(*) INTO documentExists
    FROM SDR_DOCUMENT
    WHERE ID = inDocumentId;
    IF (documentExists = 0) THEN
        outResult := -1;
        RETURN;
    END IF;

    -- select results
    OPEN outCursor FOR 
      SELECT SDR_DOCUMENT.ID,
             SDR_DOCUMENT.TITLE,
             SDR_DOCUMENT.DESCRIPTION,
             SDR_DOCUMENT.CONTENT_ID,
             SDR_DOCUMENT.REFERENCE,
             SDR_DOCUMENT_TYPE.CODE AS TYPE_CODE,
             SDR_LANGUAGE.CODE AS LANGUAGE_CODE,
             SDR_DOCUMENT.ORIGINAL_ID,
             (SELECT CODE
              FROM SDR_DOCUMENT_STATUS
              WHERE ID = (SELECT DOCUMENT_STATUS_ID
                          FROM SDR_DOCUMENT_STATUS_HISTORY
                          WHERE ID = (SELECT MAX(ID)
                                      FROM SDR_DOCUMENT_STATUS_HISTORY
                                      WHERE DOCUMENT_ID = SDR_DOCUMENT.ID))) AS STATUS_CODE,
             (SELECT ADATE
              FROM SDR_DOCUMENT_STATUS_HISTORY
              WHERE ID = (SELECT MAX(ID)
                          FROM SDR_DOCUMENT_STATUS_HISTORY
                          WHERE DOCUMENT_ID = SDR_DOCUMENT.ID)) AS STATUS_DATE,
             (SELECT LOGIN
              FROM SDR_PERSON
              WHERE ID = (SELECT PERSON_ID
                          FROM SDR_DOCUMENT_STATUS_HISTORY
                          WHERE ID = (SELECT MAX(ID)
                                      FROM SDR_DOCUMENT_STATUS_HISTORY
                                      WHERE DOCUMENT_ID = SDR_DOCUMENT.ID))) AS STATUS_AUTHOR_LOGIN
      FROM SDR_DOCUMENT INNER JOIN SDR_DOCUMENT_TYPE ON SDR_DOCUMENT.DOCUMENT_TYPE_ID = SDR_DOCUMENT_TYPE.ID
                        INNER JOIN SDR_LANGUAGE ON SDR_DOCUMENT.LANGUAGE_ID = SDR_LANGUAGE.ID
      WHERE SDR_DOCUMENT.ID <> inDocumentId                        
      START WITH SDR_DOCUMENT.ID = inDocumentId
      CONNECT BY PRIOR SDR_DOCUMENT.ORIGINAL_ID = SDR_DOCUMENT.ID;

    outResult := 0;    
END GetReplacedDocuments;

END DOCUMENT;
/
 CREATE OR REPLACE PACKAGE  "PERSON" AS

--stored procedures declarations
PROCEDURE InsertPerson
(
    inName IN SDR_PERSON.NAME%TYPE,
    inSurname IN SDR_PERSON.SURNAME%TYPE,
    inLogin IN SDR_PERSON.LOGIN%TYPE,
    inEmail IN SDR_PERSON.EMAIL%TYPe,
    outId OUT INT
);

PROCEDURE UpdatePerson
(
    inOutId IN OUT SDR_PERSON.ID%TYPE,
    inName IN SDR_PERSON.NAME%TYPE,
    inSurname IN SDR_PERSON.SURNAME%TYPE,
    inLogin IN SDR_PERSON.LOGIN%TYPE,
    inEmail IN SDR_PERSON.EMAIL%TYPE
);

PROCEDURE SetPersonStatus
(
    inPersonId IN SDR_PERSON.ID%TYPE,
    inPersonStatusCode IN SDR_PERSON_STATUS.CODE%TYPE,
    outResult OUT INT
);

PROCEDURE GetPersonById
(
    inPersonId IN SDR_Person.ID%TYPE,
    outCursor OUT SYS_REFCURSOR
);

PROCEDURE GetPersonByLogin
(
    inLogin IN SDR_Person.LOGIN%TYPE,
    outCursor OUT SYS_REFCURSOR
);

PROCEDURE GetPeopleByStatus
(
    inPersonStatusCode IN SDR_PERSON_STATUS.CODE%TYPE,
    outResult OUT INT,
    outCursor OUT SYS_REFCURSOR
);

PROCEDURE SetPasswordHash
(
    inPersonId IN SDR_PERSON.ID%TYPE,
    inPasswordHash IN SDR_PERSON.PASSWORD_HASH%TYPE,
    outResult OUT INT
);

PROCEDURE ValidateCredentials
(
    inLogin IN SDR_PERSON.LOGIN%TYPE,
    inPasswordHash IN SDR_PERSON.PASSWORD_HASH%TYPE,
    outResult OUT INT
);

PROCEDURE InsertPlayedRole
(
    inRoleCode IN SDR_ROLE.CODE%TYPE,
    inPersonId IN SDR_PLAYED_ROLE.PERSON_ID%TYPE,
    inStartDate IN SDR_PLAYED_ROLE.START_DATE%TYPE,
    inEndDate IN SDR_PLAYED_ROLE.END_DATE%TYPE,
    outId OUT SDR_PLAYED_ROLE.ID%TYPE
);

PROCEDURE SetPlayedRoleStatus
(
    inPlayedRoleId IN SDR_PLAYED_ROLE.ID%TYPE,
    inPlayedRoleStatusCode IN SDR_PLAYED_ROLE_STATUS.CODE%TYPE,
    outResult OUT INT
);

PROCEDURE GetPlayedRoles
(
    inPersonId  IN  SDR_PERSON.ID%TYPE,
    inPlayedRoleStatusCode IN SDR_PLAYED_ROLE_STATUS.CODE%TYPE,
    outResult OUT INT,
    outCursor OUT SYS_REFCURSOR
);

PROCEDURE GetPeopleInRole
(
    inRoleCode IN SDR_ROLE.CODE%TYPE,
    inPlayedRoleStatusCode IN SDR_PLAYED_ROLE_STATUS.CODE%TYPE,
    outResult OUT INT,
    outCursor OUT SYS_REFCURSOR
);

END PERSON;
/
CREATE OR REPLACE PACKAGE BODY  "PERSON" AS

/*
 * Description : Inserts a new person into the database
 * Author : Lukasz Laszko
 * Params : inName  - person name
 *          inSurname - person surname
 *          inLogin - person login
 *          inEmail - person email
 * Return values: id  - >0  - all ok, task id
 *                    - -1  - login already exists
 *                    - -2  - email already exists
 * History : 2008-05-09 - Lukasz Laszko, stored procedure created
 */
PROCEDURE InsertPerson
(
    inName IN SDR_PERSON.NAME%TYPE,
    inSurname IN SDR_PERSON.SURNAME%TYPE,
    inLogin IN SDR_PERSON.LOGIN%TYPE,
    inEmail IN SDR_PERSON.EMAIL%TYPe,
    outId OUT INT
) 
AS
    loginExists INT;
    emailExists INT;    
BEGIN
    --  check if login already exists
    SELECT COUNT(*) INTO loginExists 
    FROM SDR_PERSON 
    WHERE LOGIN = inLogin AND (ROWNUM <= 1);
    IF (loginExists <> 0) THEN
        outId := -1;
        RETURN;
    END IF;
      
    -- check if email already exists
    SELECT COUNT(*) INTO emailExists 
    FROM SDR_PERSON 
    WHERE EMAIL = inEmail AND (ROWNUM <= 1);
    IF (emailExists <> 0) THEN
        outId := -2;
        RETURN;
    END IF;
      
    -- insert person
    INSERT INTO SDR_PERSON(NAME, SURNAME, LOGIN, EMAIL)
                    VALUES(inName, inSurname, inLogin, inEmail)
                    RETURNING ID INTO outId;
END InsertPerson;

/*
 * Description : Updates existing person
 * Author : Lukasz Laszko
 * Params : inOutId  - person unique id, person with this id will be updated, error code if negative
 *          inName  - person name
 *          inSurname - person surname
 *          inLogin - person login
 *          inEmail  - person email
 * Return values: inOut      - >0  - all ok
 *                           - -1  - person does not exist
 *                           - -2  - different person has given login
 *                           - -3  - different person has given email
 * History : 2008-05-09 - Lukasz Laszko, stored procedure created
 */
PROCEDURE UpdatePerson
(
    inOutId IN OUT SDR_PERSON.ID%TYPE,
    inName IN SDR_PERSON.NAME%TYPE,
    inSurname IN SDR_PERSON.SURNAME%TYPE,
    inLogin IN SDR_PERSON.LOGIN%TYPE,
    inEmail IN SDR_PERSON.EMAIL%TYPE
)
AS
    personExists INT;
    loginInValid INT;
    emailInValid INT;
BEGIN
    -- check if person exists
    SELECT COUNT(*) INTO personExists
    FROM SDR_PERSON
    WHERE ID = inOutId;
    IF (personExists = 0) THEN
        inOutId := -1;
        RETURN;
    END IF;
    
    -- check if there is different person with this login
    SELECT COUNT(*) INTO loginInValid
    FROM SDR_PERSON
    WHERE (ID <> inOutId) AND (LOGIN = inLogin) AND (ROWNUM <= 1);
    IF (loginInValid = 1) THEN
      inOutId := -2;
      RETURN;
    END IF;
    
    -- check if there is different person with this email
    SELECT COUNT(*) INTO emailInValid
    FROM SDR_PERSON
    WHERE (ID <> inOutId) AND (LOGIN = inEmail) AND (ROWNUM <= 1);
    IF (emailInValid = 1) THEN
      inOutId := -3;
      RETURN;
    END IF;
    
    -- update person
    UPDATE SDR_PERSON
    SET   NAME = inName,
          SURNAME = inSurname,
          LOGIN = inLogin,
          EMAIL = inEmail
    WHERE ID = inOutId;
    
END UpdatePerson;

/*
 * Description : Inserts new person status
 * Author : Lukasz Laszko
 * Params : inPersonId  - person id
 *          inPersonStatusCode  - person status code
 *          outResult - result code
 * Return values: id  - 0  - all ok
 *                    - -1  - person doesn't exist
 *                    - -2  - person status code is invalid
 * History : 2008-05-09 - Lukasz Laszko, stored procedure created
 */
PROCEDURE SetPersonStatus
(
    inPersonId IN SDR_PERSON.ID%TYPE,
    inPersonStatusCode IN SDR_PERSON_STATUS.CODE%TYPE,
    outResult OUT INT
)
AS
    personExists INT;
    personStatusExists INT;
    personStatusId SDR_PERSON_STATUS.ID%TYPE; 
BEGIN    
    -- check if person exists
    SELECT COUNT(*) INTO personExists
    FROM SDR_PERSON
    WHERE (ID = inPersonId) AND (ROWNUM <= 1);
    IF (personExists = 0) THEN
        outResult := -1;
        RETURN;
    END IF;
    
    -- get personStatusId
    SELECT COUNT(*) INTO personStatusExists
    FROM SDR_PERSON_STATUS
    WHERE (CODE = inPersonStatusCode) AND (ROWNUM <= 1);
    IF (personStatusExists = 0) THEN
        outResult := -2;
        RETURN;
    ELSE
         SELECT ID INTO personStatusId
         FROM SDR_PERSON_STATUS
         WHERE (CODE = inPersonStatusCode) AND (ROWNUM <= 1);
    END IF;
    
    -- insert status
    INSERT INTO SDR_PERSON_STATUS_HISTORY (PERSON_ID, PERSON_STATUS_ID, ADATE)
                            VALUES(inPersonId, personStatusId, CURRENT_TIMESTAMP);      
                            
    outResult := 0;
END SetPersonStatus;

/*
 * Description : Gets person by its id
 * Author : Lukasz Laszko
 * Params : inPersonId  - person id
 *          outCursor - result cursor
 * History : 2008-05-09 - Lukasz Laszko, stored procedure created
 */
PROCEDURE GetPersonById
(
    inPersonId IN SDR_Person.ID%TYPE,
    outCursor OUT SYS_REFCURSOR
)
AS
BEGIN

    OPEN outCursor FOR 
        SELECT  SDR_PERSON.ID,
                SDR_PERSON.NAME,
                SDR_PERSON.SURNAME,
                SDR_PERSON.LOGIN,
                SDR_PERSON.EMAIL,
                (SELECT SDR_PERSON_STATUS.CODE
                 FROM SDR_PERSON_STATUS INNER JOIN SDR_PERSON_STATUS_HISTORY ON SDR_PERSON_STATUS.ID = SDR_PERSON_STATUS_HISTORY.PERSON_STATUS_ID
                 WHERE (SDR_PERSON_STATUS_HISTORY.PERSON_ID = SDR_PERSON.ID) AND 
                       (ROWNUM <= 1) AND 
                       (SDR_PERSON_STATUS_HISTORY.ID = (SELECT MAX(ID) 
                                                        FROM SDR_PERSON_STATUS_HISTORY
                                                        WHERE SDR_PERSON_STATUS_HISTORY.PERSON_ID = SDR_PERSON.ID))) AS STATUS_CODE,
                (SELECT MAX(ADATE) 
                 FROM SDR_PERSON_STATUS_HISTORY
                 WHERE SDR_PERSON_STATUS_HISTORY.PERSON_ID = SDR_PERSON.ID) AS STATUS_DATE
        FROM    SDR_PERSON
        WHERE   ID = inPersonId;
    
END GetPersonById;

/*
 * Description : Gets person by its login
 * Author : Lukasz Laszko
 * Params : inLogin  - person login
 *          outCursor - result cursor
 * History : 2008-05-09 - Lukasz Laszko, stored procedure created
 */
PROCEDURE GetPersonByLogin
(
    inLogin IN SDR_Person.LOGIN%TYPE,
    outCursor OUT SYS_REFCURSOR
)
AS
BEGIN

    OPEN outCursor FOR 
        SELECT  SDR_PERSON.ID,
                SDR_PERSON.NAME,
                SDR_PERSON.SURNAME,
                SDR_PERSON.LOGIN,
                SDR_PERSON.EMAIL,
                (SELECT SDR_PERSON_STATUS.CODE
                 FROM SDR_PERSON_STATUS INNER JOIN SDR_PERSON_STATUS_HISTORY ON SDR_PERSON_STATUS.ID = SDR_PERSON_STATUS_HISTORY.PERSON_STATUS_ID
                 WHERE (SDR_PERSON_STATUS_HISTORY.PERSON_ID = SDR_PERSON.ID) AND 
                       (ROWNUM <= 1) AND 
                       (SDR_PERSON_STATUS_HISTORY.ID = (SELECT MAX(ID) 
                                                        FROM SDR_PERSON_STATUS_HISTORY
                                                        WHERE SDR_PERSON_STATUS_HISTORY.PERSON_ID = SDR_PERSON.ID))) AS STATUS_CODE,
                (SELECT MAX(ADATE) 
                 FROM SDR_PERSON_STATUS_HISTORY
                 WHERE SDR_PERSON_STATUS_HISTORY.PERSON_ID = SDR_PERSON.ID) AS STATUS_DATE
        FROM    SDR_PERSON
        WHERE   LOGIN = inLogin;
    
END GetPersonByLogin;

/*
 * Description : Gets list of users currently in given status
 * Author : Lukasz Laszko
 * Params : inPersonStatusCode  - person status code
 *          outCursor - result cursor 
 * Return values: 0 - all ok
 *               -1 - invalid person status code
 * History : 2008-05-10 - Lukasz Laszko, stored procedure created
 */
PROCEDURE GetPeopleByStatus
(
    inPersonStatusCode IN SDR_PERSON_STATUS.CODE%TYPE,
    outResult OUT INT,
    outCursor OUT SYS_REFCURSOR
)
AS
    personStatusExists INT;
    personStatusId INT;
BEGIN
    -- check if person status code exists
    SELECT COUNT(*) INTO personStatusExists
    FROM  SDR_PERSON_STATUS 
    WHERE (CODE = inPersonStatusCode) AND (ROWNUM <= 1);
    IF (personStatusExists = 0) THEN
        outResult := -1;
        RETURN;    
    END IF;
    
    -- get personStatusId
    SELECT ID INTO personStatusId
    FROM  SDR_PERSON_STATUS
    WHERE (CODE = inPersonStatusCode) AND (ROWNUM <= 1);
    
    -- select people
    OPEN outCursor FOR
        SELECT  SDR_PERSON.ID,
                SDR_PERSON.NAME,
                SDR_PERSON.SURNAME,
                SDR_PERSON.LOGIN,
                SDR_PERSON.EMAIL,
                (SELECT SDR_PERSON_STATUS.CODE
                 FROM SDR_PERSON_STATUS INNER JOIN SDR_PERSON_STATUS_HISTORY ON SDR_PERSON_STATUS.ID = SDR_PERSON_STATUS_HISTORY.PERSON_STATUS_ID
                 WHERE (SDR_PERSON_STATUS_HISTORY.PERSON_ID = SDR_PERSON.ID) AND 
                       (ROWNUM <= 1) AND 
                       (SDR_PERSON_STATUS_HISTORY.ID = (SELECT MAX(ID) 
                                                        FROM SDR_PERSON_STATUS_HISTORY
                                                        WHERE SDR_PERSON_STATUS_HISTORY.PERSON_ID = SDR_PERSON.ID))) AS STATUS_CODE,
                (SELECT MAX(ADATE) 
                 FROM SDR_PERSON_STATUS_HISTORY
                 WHERE SDR_PERSON_STATUS_HISTORY.PERSON_ID = SDR_PERSON.ID) AS STATUS_DATE
        FROM    SDR_PERSON
        WHERE   ID IN (SELECT SDR_PERSON.ID
                       FROM  SDR_PERSON  INNER JOIN SDR_PERSON_STATUS_HISTORY ON SDR_PERSON.ID = SDR_PERSON_STATUS_HISTORY.PERSON_ID
                       WHERE (SDR_PERSON_STATUS_HISTORY.ID = (SELECT MAX(SDR_PERSON_STATUS_HISTORY.ID) 
                                                              FROM SDR_PERSON_STATUS_HISTORY
                                                              WHERE PERSON_ID = SDR_PERSON.ID)) AND
                             (SDR_PERSON_STATUS_HISTORY.PERSON_STATUS_ID = personStatusId));    
    
    outResult := 0;
END GetPeopleByStatus;

/*
 * Description : Sets password hash for person
 * Author : Lukasz Laszko
 * Params : inPersonId  - person id
 *          inPasswordHash  - password SH1 hash
 *          outResult - error code
 * Return values: 0 - all ok
 *               -1 - invalid person id
 * History : 2008-05-10 - Lukasz Laszko, stored procedure created
 */
PROCEDURE SetPasswordHash
(
    inPersonId IN SDR_PERSON.ID%TYPE,
    inPasswordHash IN SDR_PERSON.PASSWORD_HASH%TYPE,
    outResult OUT INT
)
AS
    personExists INT;
BEGIN
    -- check if person exists
    SELECT COUNT(*) INTO personExists
    FROM SDR_PERSON
    WHERE (ID = inPersonId) AND (ROWNUM <= 1);
    IF (personExists = 0) THEN
        outResult := -1;
        RETURN;
    END IF;
    
    -- set password hash
    UPDATE SDR_PERSON
    SET PASSWORD_HASH = inPasswordHash
    WHERE ID = inPersonId;
   
    outResult := 0;
END SetPasswordHash;

/*
 * Description : Validates if person with given login exists in datbase and if 
 *               the given password hash is valid
 * Author : Lukasz Laszko
 * Params : inLogin  - person login
 *          inPasswordHash  - password SH1 hash
 *          outResult - error code
 * Return values: 0 - both login and password hash are valid
 *               -1 - invalid credentials
 * History : 2008-05-10 - Lukasz Laszko, stored procedure created
 */
PROCEDURE ValidateCredentials
(
    inLogin IN SDR_PERSON.LOGIN%TYPE,
    inPasswordHash IN SDR_PERSON.PASSWORD_HASH%TYPE,
    outResult OUT INT
)
AS
    personExists INT;
BEGIN
    SELECT COUNT(*) INTO personExists
    FROM SDR_PERSON
    WHERE (LOGIN = inLogin) AND 
          (PASSWORD_HASH = inPasswordHash) AND
          (ROWNUM <= 1);
    IF (personExists = 1) THEN
        outResult := 0;
        RETURN;
    ELSE
        outResult := -1;
        RETURN;
    END IF;
END ValidateCredentials;

/*
 * Description : Insert role played by the person
 * Author : Lukasz Laszko
 * Params : inRoleCode  - role type code
 *          inPersonId  - person id
 *          inStartDate - start date
 *          inEndDate - end date
 *          outId - error code or new played role id
 * Return values: >0 - all ok, new played role id
 *               -1 - invalid role code
 *               -2 - person does not exist
 * History : 2008-05-10 - Lukasz Laszko, stored procedure created
 */
PROCEDURE InsertPlayedRole
(
    inRoleCode IN SDR_ROLE.CODE%TYPE,
    inPersonId IN SDR_PLAYED_ROLE.PERSON_ID%TYPE,
    inStartDate IN SDR_PLAYED_ROLE.START_DATE%TYPE,
    inEndDate IN SDR_PLAYED_ROLE.END_DATE%TYPE,
    outId OUT SDR_PLAYED_ROLE.ID%TYPE
)
AS
    roleCodeExists INT;
    roleId INT;
    personExists INT;    
BEGIN
    -- check if role code exists
    SELECT COUNT(*) INTO roleCodeExists
    FROM  SDR_ROLE
    WHERE (CODE = inRoleCode) AND (ROWNUM <= 1);
    IF (roleCodeExists = 0) THEN
        outId := -1;
        RETURN;
    END IF;
    
    -- get role id
    SELECT ID INTO roleId
    FROM SDR_ROLE
    WHERE (CODE = inRoleCode) AND (ROWNUM <= 1);
    
    -- check if person exists
    SELECT COUNT(*) INTO personExists
    FROM SDR_PERSON
    WHERE (ID = inPersonId) AND (ROWNUM <= 1);
    IF (personExists = 0) THEN
        outId := -2;
        RETURN;
    END IF;
    
    -- insert played role
    INSERT INTO SDR_PLAYED_ROLE(ROLE_ID, PERSON_ID, START_DATE, END_DATE)
                         VALUES(roleId, inPersonId, inStartDate, inEndDate)
                         RETURNING ID INTO outId;
END InsertPlayedRole;

/*
 * Description : Sets played role status
 * Author : Lukasz Laszko
 * Params : inPlayedRoleId  - plyaed role id
 *          inPlayedRoleStatusCode  - played role code status
 *          outResult - error code
 * Return values: 0 - all ok
 *               -1 - played role doesn't exist
 *               -2 - played role status code is invalid
 * History : 2008-05-10 - Lukasz Laszko, stored procedure created
 */
PROCEDURE SetPlayedRoleStatus
(
    inPlayedRoleId IN SDR_PLAYED_ROLE.ID%TYPE,
    inPlayedRoleStatusCode IN SDR_PLAYED_ROLE_STATUS.CODE%TYPE,
    outResult OUT INT
)
AS
    playedRoleExists INT;
    playedRoleStatusCodeExists INT;
    playedRoleStatusId SDR_PLAYED_ROLE_STATUS.ID%TYPE;
BEGIN
    --  check if played role exists
    SELECT COUNT(*) INTO playedRoleExists
    FROM SDR_PLAYED_ROLE
    WHERE (ID = inPlayedRoleId) AND (ROWNUM <= 1);
    IF (playedRoleExists = 0) THEN
        outResult := -1;
        RETURN;
    END IF;
    
    -- check if played role status code is valid
    SELECT COUNT(*) INTO playedRoleStatusCodeExists
    FROM SDR_PLAYED_ROLE_STATUS
    WHERE (CODE = inPlayedRoleStatusCode) AND (ROWNUM <= 1);
    IF (playedRoleStatusCodeExists = 0) THEN
        outResult := -2;
        RETURN;
    END IF;
    
    -- get playedRoleStatusId
    SELECT ID INTO playedRoleStatusId
    FROM SDR_PLAYED_ROLE_STATUS
    WHERE (CODE = inPlayedRoleStatusCode) AND (ROWNUM <= 1);
    
    -- insert history entry
    INSERT INTO SDR_PLAYED_ROLE_STATUS_HISTORY(PLAYED_ROLE_ID, PLAYED_ROLE_STATUS_ID, ADATE)
                                        VALUES(inPlayedRoleId, playedRoleStatusId, CURRENT_TIMESTAMP);
                                        
    outResult := 0;                                        
END SetPlayedRoleStatus;

/*
 * Description : Gets list of roles played by the given person in given state
 * Author : Lukasz Laszko
 * Params : inPersonId - person id
 *          inPlayedRoleStatus - played role status
 *          outResult - error code
 * Return values: 0 - all ok
 *               -1 - person does not exist
 * History : 2008-05-10 - Lukasz Laszko, stored procedure created
 */
PROCEDURE GetPlayedRoles
(
    inPersonId  IN  SDR_PERSON.ID%TYPE,
    inPlayedRoleStatusCode IN SDR_PLAYED_ROLE_STATUS.CODE%TYPE,
    outResult OUT INT,
    outCursor OUT SYS_REFCURSOR
)
AS
    personExists INT;
    playedRoleStatusCodeExists INT;
    playedRoleStatusId SDR_PLAYED_ROLE_STATUS.ID%TYPE;
BEGIN 
    -- check if person exists
    SELECT COUNT(*) INTO personExists
    FROM SDR_PERSON
    WHERE (ID = inPersonId) AND (ROWNUM <= 1);
    IF (personExists = 0) THEN
        outResult := -1;
        RETURN;
    END IF;
    
    -- check if played role status code is valid
    SELECT COUNT(*) INTO playedRoleStatusCodeExists
    FROM SDR_PLAYED_ROLE_STATUS
    WHERE (CODE = inPlayedRoleStatusCode) AND (ROWNUM <= 1);
    IF (playedRoleStatusCodeExists = 0) THEN
        outResult := -2;
        RETURN;
    END IF;
    
    -- get playedRoleStatusId
    SELECT ID INTO playedRoleStatusId
    FROM SDR_PLAYED_ROLE_STATUS
    WHERE (CODE = inPlayedRoleStatusCode) AND (ROWNUM <= 1);
    
    -- get roles played by the person
    OPEN outCursor FOR
      SELECT DISTINCT  SDR_PLAYED_ROLE.ID,
                       SDR_PLAYED_ROLE.PERSON_ID,
                       SDR_ROLE.CODE AS ROLE_CODE,
                       SDR_PLAYED_ROLE.START_DATE,
                       SDR_PLAYED_ROLE.END_DATE,
                       (SELECT CODE
                        FROM SDR_PLAYED_ROLE_STATUS INNER JOIN SDR_PLAYED_ROLE_STATUS_HISTORY ON SDR_PLAYED_ROLE_STATUS.ID = SDR_PLAYED_ROLE_STATUS_HISTORY.PLAYED_ROLE_STATUS_ID
                        WHERE (SDR_PLAYED_ROLE_STATUS_HISTORY.PLAYED_ROLE_ID = SDR_PLAYED_ROLE.ID) AND 
                              (ROWNUM <= 1) AND 
                              (SDR_PLAYED_ROLE_STATUS_HISTORY.ID = (SELECT MAX(ID) 
                                                                    FROM SDR_PLAYED_ROLE_STATUS_HISTORY
                                                                    WHERE SDR_PLAYED_ROLE_STATUS_HISTORY.PLAYED_ROLE_ID = SDR_PLAYED_ROLE.ID))) AS STATUS_CODE,
                        (SELECT MAX(ADATE) 
                         FROM SDR_PLAYED_ROLE_STATUS_HISTORY
                         WHERE SDR_PLAYED_ROLE_STATUS_HISTORY.PLAYED_ROLE_ID = SDR_PLAYED_ROLE.ID) AS STATUS_DATE
                FROM   SDR_PLAYED_ROLE  INNER JOIN SDR_PLAYED_ROLE_STATUS_HISTORY ON SDR_PLAYED_ROLE.ID = SDR_PLAYED_ROLE_STATUS_HISTORY.PLAYED_ROLE_ID
                                        INNER JOIN SDR_ROLE ON SDR_PLAYED_ROLE.ROLE_ID = SDR_ROLE.ID
                WHERE  (SDR_PLAYED_ROLE.PERSON_ID = inPersonId) AND 
                       ((SELECT SDR_PLAYED_ROLE_STATUS_HISTORY.PLAYED_ROLE_STATUS_ID
                         FROM SDR_PLAYED_ROLE_STATUS_HISTORY 
                         WHERE SDR_PLAYED_ROLE_STATUS_HISTORY.ID = (SELECT MAX(ID) 
                                                                    FROM SDR_PLAYED_ROLE_STATUS_HISTORY
                                                                    WHERE SDR_PLAYED_ROLE_STATUS_HISTORY.PLAYED_ROLE_ID = SDR_PLAYED_ROLE.ID)) = playedRoleStatusId); 
              
    outResult := 0;
END GetPlayedRoles;

/*
 * Description : Gets list of people in th given rile if role 
 *               is in the specified status
 * Author : Lukasz Laszko
 * Params : inRoleCode  - role type code
 *          inPlayedRoleStatusCode  - played role status code
 *          outResult - error code
 *          outCursor - ref cursor to result data set
 * Return values: 0 - all ok
 *               -1 - role type code is invalid
 *               -2 - played role status code is iinvalid
 * History : 2008-05-11 - Lukasz Laszko, stored procedure created
 */
PROCEDURE GetPeopleInRole
(
    inRoleCode IN SDR_ROLE.CODE%TYPE,
    inPlayedRoleStatusCode IN SDR_PLAYED_ROLE_STATUS.CODE%TYPE,
    outResult OUT INT,
    outCursor OUT SYS_REFCURSOR
)
AS
    roleCodeExists INT;
    roleId SDR_ROLE.ID%TYPE;
    playedRoleStatusCodeExists INT;
    playedRoleStatusId SDR_PLAYED_ROLE_STATUS.ID%TYPE;
BEGIN
    -- check if role code is valid
    SELECT COUNT(*) INTO roleCodeExists
    FROM SDR_ROLE
    WHERE (CODE = inRoleCode) AND (ROWNUM <= 1);
    IF (roleCodeExists = 0) THEN
        outResult := -1;
        RETURN;
    END IF;
    
    -- select role id
    SELECT ID INTO roleId
    FROM SDR_ROLE
    WHERE (CODE = inRoleCode) AND (ROWNUM <= 1);
    
    -- check if status code exists
    SELECT COUNT(*) INTO playedRoleStatusCodeExists
    FROM SDR_PLAYED_ROLE_STATUS
    WHERE (CODE = inPlayedRoleStatusCode) AND (ROWNUM <= 1);
    IF (playedRoleStatusCodeExists = 0) THEN
        outResult := -2;
        RETURN;
    END IF;
    
    -- select played role status id
    SELECT ID INTO playedRoleStatusId
    FROM SDR_PLAYED_ROLE_STATUS
    WHERE (CODE = inPlayedRoleStatusCode) AND (ROWNUM <= 1);
    
      -- select list of people
    OPEN outCursor FOR
      SELECT  SDR_PERSON.ID,
              SDR_PERSON.NAME,
              SDR_PERSON.SURNAME,
              SDR_PERSON.LOGIN,
              SDR_PERSON.EMAIL,
              (SELECT CODE 
               FROM SDR_PERSON_STATUS
               WHERE ID = (SELECT PERSON_STATUS_ID
                           FROM SDR_PERSON_STATUS_HISTORY
                           WHERE ID = (SELECT MAX(ID)
                                       FROM SDR_PERSON_STATUS_HISTORY
                                       WHERE PERSON_ID = SDR_PERSON.ID))) AS STATUS_CODE,
              (SELECT MAX(ADATE) 
               FROM SDR_PERSON_STATUS_HISTORY
               WHERE SDR_PERSON_STATUS_HISTORY.PERSON_ID = SDR_PERSON.ID) AS STATUS_DATE
      FROM    SDR_PERSON
      WHERE   ID IN (SELECT SDR_PLAYED_ROLE.PERSON_ID
                     FROM SDR_PLAYED_ROLE
                     WHERE (ROLE_ID = roleId) AND 
                           (ID = (SELECT PLAYED_ROLE_ID
                                  FROM SDR_PLAYED_ROLE_STATUS_HISTORY
                                  WHERE (PLAYED_ROLE_STATUS_ID = playedRoleStatusId) AND (ID = (SELECT MAX(ID)
                                                                                                FROM SDR_PLAYED_ROLE_STATUS_HISTORY
                                                                                                WHERE PLAYED_ROLE_ID = SDR_PLAYED_ROLE.ID)))));
      
    outResult := 0;
END GetPeopleInRole;

END PERSON;
/
 
Rem No procedure found to generate DDL.
Rem No synonym found to generate DDL.
CREATE OR REPLACE FORCE VIEW  "SDR_VIEW_ANNOTATION" OF  "T_DESCRIPTOR"
  WITH OBJECT IDENTIFIER (ANNOTATION_ID) AS 
  SELECT  SDR_ANNOTATION.ID,
            SDR_ANNOTATION.DESCRIPTOR
    FROM    SDR_ANNOTATION
/
CREATE OR REPLACE FORCE VIEW  "SDR_VIEW_TOKEN_REFERENCE" OF  "T_TOKEN_REFERENCE"
  WITH OBJECT IDENTIFIER (REFERENCE_ID) AS 
  SELECT SDR_TOKEN_REFERENCE.ID,
         SDR_TOKEN.VALUE,
         SDR_TOKEN_REFERENCE.PROBABILITY,
         SDR_TOKEN_REFERENCE.ACOUSTIC_SCORE,
         SDR_TOKEN_REFERENCE.OFFSET
  FROM   SDR_TOKEN_REFERENCE INNER JOIN SDR_TOKEN ON SDR_TOKEN_REFERENCE.TOKEN_ID = SDR_TOKEN.ID
/

